package com.xiyi.shouxin.dao.user;

import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;

import com.xiyi.shouxin.dao.BaseRepository;
import com.xiyi.shouxin.dao.JdbcPreparedStatement;
import com.xiyi.shouxin.entity.user.Cpr;
import com.xiyi.shouxin.entity.user.mapper.CprMapper;
import com.xiyi.shouxin.exception.CommonException;
import com.xiyi.shouxin.exception.ErrorCode;
import com.xiyi.shouxin.global.QualifiedEnum;

@Repository
public class CprRepository extends BaseRepository<Cpr> {
    public CprRepository() {
        super();
        super.stat = new JdbcPreparedStatement<>(Cpr.class);
    }

    public List<Cpr> findByMultiConditionsByPage(Long uid, Long practiceId, Sort sort,
            int page, int size) throws Exception {
        List<Object> args = new ArrayList<>();
        List<String> conditions = new ArrayList<>();
        if (uid != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("uid"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(uid);
        }
        if (practiceId != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("practiceId"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(practiceId);
        }
        return findByMultiConditionsByPage(conditions, args, sort, page, size, new CprMapper());
    }

    public Integer countByMultiConditions(Long uid, Long practiceId) {
        List<Object> args = new ArrayList<>();
        List<String> conditions = new ArrayList<>();
        if (uid != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("uid"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(uid);
        }
        if (practiceId != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("practiceId"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(practiceId);
        }
        return countByMultiConditions(conditions, args);
    }

    public Cpr findFirstByPracticeIdOrderByCreateTime(Long practiceId) throws CommonException {
        if (practiceId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT ");
        sb.append(stat.GetQueryColumns());
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ORDER BY ");
        sb.append(stat.GetColumnName("createdAt"));
        sb.append(" ASC LIMIT 1");
        try {
            Cpr cpr = template.queryForObject(sb.toString(), new Object[] { practiceId }, new CprMapper());
            return cpr;
        } catch (EmptyResultDataAccessException e) {

        }
        return null;
    }

    public Cpr findLastByPracticeIdOrderByCreateTime(Long practiceId) throws CommonException {
        if (practiceId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT ");
        sb.append(stat.GetQueryColumns());
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ORDER BY ");
        sb.append(stat.GetColumnName("createdAt"));
        sb.append(" DESC LIMIT 1");
        try {
            Cpr cpr = template.queryForObject(sb.toString(), new Object[] { practiceId }, new CprMapper());
            return cpr;
        } catch (EmptyResultDataAccessException e) {

        }
        return null;
    }

    public void notResetLastByPracticeIdOrderByCreateTime(Long practiceId) throws CommonException {
        if (practiceId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("UPDATE ");
        sb.append(stat.GetTableName());
        sb.append(" SET ");
        sb.append(stat.GetColumnName("qualified"));
        sb.append(" = ?");
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ORDER BY ");
        sb.append(stat.GetColumnName("createdAt"));
        sb.append(" DESC LIMIT 1");
        template.update(sb.toString(), QualifiedEnum.NOTRESET.getQualified(), practiceId);
    }

    public Integer countByPracticeId(Long practiceId)
            throws CommonException {
        if (practiceId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT COUNT(1)");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ");
        try {
            Integer count = template.queryForObject(sb.toString(), Integer.class, practiceId);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Integer countByPracticeIdAndQualified(Long practiceId, String qualified) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1)");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("qualified"));
        sb.append(" = ? ");
        try {
            Integer count = template.queryForObject(sb.toString(), Integer.class, practiceId, qualified);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Double avgDepthByPracticeId(Long practiceId) {
        StringBuilder sb = new StringBuilder("SELECT ROUND(AVG(");
        sb.append(stat.GetColumnName("depth"));
        sb.append("),2) ");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ");
        try {
            Double avg = template.queryForObject(sb.toString(), Double.class, practiceId);
            return avg;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0d;
    }

    public Double minDepthByPracticeId(Long practiceId) {
        StringBuilder sb = new StringBuilder("SELECT MIN(");
        sb.append(stat.GetColumnName("depth"));
        sb.append(") ");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ");
        try {
            Double min = template.queryForObject(sb.toString(), Double.class, practiceId);
            return min;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0d;
    }

    public Double maxDepthByPracticeId(Long practiceId) {
        StringBuilder sb = new StringBuilder("SELECT MAX(");
        sb.append(stat.GetColumnName("depth"));
        sb.append(") ");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ");
        try {
            Double max = template.queryForObject(sb.toString(), Double.class, practiceId);
            return max;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0d;
    }

    public Integer countByPracticeIdAndGreaterThenDepth(Long practiceId, Double depth) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1)");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE  practice_id = ? AND depth > ?");
        try {
            Integer count = template.queryForObject(sb.toString(), Integer.class, practiceId, depth);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Integer countByPracticeIdAndLessThenDepth(Long practiceId, Double depth) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1)");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE  practice_id = ? AND depth < ?");
        try {
            Integer count = template.queryForObject(sb.toString(), Integer.class, practiceId, depth);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Long avgTimeByPracticeId(Long practiceId) {
        StringBuilder sb = new StringBuilder("SELECT ROUND(AVG(");
        sb.append(stat.GetColumnName("time"));
        sb.append("),2) ");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ");
        try {
            Long avg = template.queryForObject(sb.toString(), Long.class, practiceId);
            return avg;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long minTimeByPracticeId(Long practiceId) {
        StringBuilder sb = new StringBuilder("SELECT MIN(");
        sb.append(stat.GetColumnName("time"));
        sb.append(") ");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ");
        try {
            Long min = template.queryForObject(sb.toString(), Long.class, practiceId);
            return min;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long maxTimeByPracticeId(Long practiceId) {
        StringBuilder sb = new StringBuilder("SELECT MAX(");
        sb.append(stat.GetColumnName("time"));
        sb.append(") ");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? ");
        try {
            Long max = template.queryForObject(sb.toString(), Long.class, practiceId);
            return max;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Integer countByPracticeIdBetweenTime(Long practiceId, Long stime, Long eTime) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1)");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("time"));
        sb.append(" BETWEEN ? AND ?");
        try {
            Integer count = template.queryForObject(sb.toString(), Integer.class, practiceId, stime, eTime);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Integer countByPracticeIdAndGreaterThenTime(Long practiceId, Long time) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1)");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("time"));
        sb.append(" > ?");
        try {
            Integer count = template.queryForObject(sb.toString(), Integer.class, practiceId, time);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Integer countByPracticeIdAndLessThenTime(Long practiceId, Long time) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1)");
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("time"));
        sb.append(" < ?");
        try {
            Integer count = template.queryForObject(sb.toString(), Integer.class, practiceId, time);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public void delByPracticeId(Long practiceId) throws CommonException {
        if (practiceId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("DELETE FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" = ?");
        template.update(sb.toString(), practiceId);
    }

    public void delByPracticeIds(List<Long> practiceIds) throws CommonException {
        if (practiceIds == null || practiceIds.isEmpty()) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("DELETE FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("practiceId"));
        sb.append(" IN(");
        List<String> els = new ArrayList<>();
        practiceIds.forEach(id -> els.add("?"));
        sb.append(String.join(",", els));
        sb.append(" )");
        template.update(sb.toString(), practiceIds.toArray());
    }

    public Long countTotal() {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1) FROM ");
        sb.append(stat.GetTableName());
        try {
            Long count = template.queryForObject(sb.toString(), Long.class);
            return count;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long countQualified() {
        StringBuilder sb = new StringBuilder("SELECT COUNT(1) FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("qualified"));
        sb.append(" = '合格' ");
        try {
            Long total = template.queryForObject(sb.toString(), Long.class);
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }
}
